﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;

using System.Text;
using System.Windows.Forms;
using QUANLYCHAMCONG.BL.BUS;
using System.Reflection;
using QUANLYCHAMCONG.BL.Entities;
using Hopthoai;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic.CompilerServices;
using Microsoft.VisualBasic;
using Excel=Microsoft.Office.Interop.Excel;
namespace QUANLYCHAMCONG.PL.CMS.BaoCao
{
    public partial class Chitietchamcong : UserControl
    {
        public Chitietchamcong()
        {
            InitializeComponent();
        }
        System.Data.DataTable dt = new System.Data.DataTable();
        public static Microsoft.Office.Interop.Excel.Application ExApp;
        public static Microsoft.Office.Interop.Excel.Workbook TasWBook;
        public static Microsoft.Office.Interop.Excel.Worksheet TasWSheet = new Microsoft.Office.Interop.Excel.Worksheet();
        public void loadPhongban()
        {
            System.Data.DataTable phongban = new System.Data.DataTable();
            phongban = PHONGBANBUS.SelectAll();
            if (phongban.Rows.Count != 0)
            {
                Common.AddDataForCombobox(ref this.cmbphongban, ref phongban, "TENPB", "MAPB");
            }
        }
        private void SetLink()
        {
            this.links1.SetValues("Báo cáo", "Chấm công chi tiết");
        }
        public void TasStartExcel([Optional, DefaultParameterValue("Sheet1")] string SheetName)
        {
            try
            {
                ExApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                TasWBook = ExApp.Workbooks.Add(Missing.Value);
                TasWSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExApp.Worksheets["Sheet1"];
                TasWSheet.Name = SheetName;
                ExApp.Visible = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
        public System.Drawing.Point DoPhanGiaiManhinh()
        {
            System.Drawing.Point p = new System.Drawing.Point();
            Size s = SystemInformation.WorkingArea.Size;
            p.X = Size.Width / 2;
            p.Y = Size.Height / 2;
            return p;
        }

        private void Chitietchamcong_Load(object sender, EventArgs e)
        {
            SetLink();
            loadPhongban();
            this.cmbloaibc.SelectedIndex = 0;
        }

        private void btnxem_Click(object sender, EventArgs e)
        {
            string MAPB;
            if (cmbphongban.SelectedIndex != -1 && cmbphongban.SelectedValue.ToString() != "")
            {
                if (DateTime.Compare(dtpbd.Value, dtpkt.Value) < 0)
                {
                    MAPB = cmbphongban.SelectedValue.ToString();
                    if (!String.IsNullOrEmpty(MAPB))
                    {
                        BAOCAO bc = new BAOCAO();
                        bc.MAPB = MAPB;
                        bc.MANV = null;
                        bc.NGAYBD = dtpbd.Value.Date;
                        bc.NGAYKT = dtpkt.Value.Date;
                        frmwaiting f = new frmwaiting();
                        f.StartPosition = FormStartPosition.CenterScreen;
                        f.Show();
                        f.lbgoi.Text = "Đang duyệt....";
                        System.Windows.Forms.Application.DoEvents();
                        dt = BAOCAOBUS.TONGCONG(bc);
                        dgvbangchamcong.DataSource = dt;
                        dgvbangchamcong.Columns["MANV"].Frozen = true;
                        dgvbangchamcong.Columns["TENNV"].Frozen = true;
                        dgvbangchamcong.Columns["TENPB"].Frozen = true;
                        dgvbangchamcong.Columns["TENCV"].Frozen = true;
                        dgvbangchamcong.Columns["NGAY"].Frozen = true;    
                        f.Close();
                    }
                }
                else
                {
                    frmHoidap hoidap = new frmHoidap("Không thể chọn ngày bắt đầu nhỏ hơn ngày kết thúc", Hopthoai.frmHoidap.CO.an, Hopthoai.frmHoidap.KHONG.an, Hopthoai.frmHoidap.THOAT.an, Hopthoai.frmHoidap.HINH.baoloi);
                    hoidap.ShowDialog();
                }

            }
        }

        private void btnin_Click(object sender, EventArgs e)
        {
            // kiem tra chon in  loai nao
            int TypePrint = -1;
            if (cmbloaibc.SelectedIndex != -1 && cmbloaibc.Text != "")
            { 
                //set typeprint 
                TypePrint = this.cmbloaibc.SelectedIndex;
                if (dgvbangchamcong.Rows.Count != 0)
                {
                    if (TypePrint != -1)
                    {

                        // thuc hien kiem tra 
                        if (TypePrint == 0)//tuc la cham cong
                        {
                            InChiTiet();
                        }
                        if (TypePrint == 1)//la ve somn
                        {
                            InVeSom();
                        }
                        if (TypePrint == 2)//tuc di tre
                        {
                            InDiTre();
                        }
                        if (TypePrint == 3)//tuc la vang
                        {
                            InVang();
                        }
                        if (TypePrint == 4)// lam cai thong ke truoc
                        {
                            ChamcongThang();
                        }
                        if (TypePrint == 5)
                        {
                            InDetailNV();
                        }
                        if (TypePrint == 6)
                        {
                            InTongCong();
                        }
                    }
                }
                else
                {
                    frmHoidap f = new frmHoidap("Không có dữ liệu", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                    f.ShowDialog();
                }
            }
        }
        #region "In chi tiết chấm công"
        void HeaderChiTiet(int StartRow, DateTime tungay, DateTime denngay)
        {
            int num2 = StartRow + 3;
            Worksheet tasWSheet = TasWSheet;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":W" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).set_Value(Missing.Value, "BẢNG CHI TIẾT CHẤM CỘNG");
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Size = 14;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":W" + Conversions.ToString((int)(num2 - 3)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":W" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).set_Value(Missing.Value, "Từ ngày " + Conversions.ToString(tungay) + " đến ngày " + Conversions.ToString(denngay));
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Size = 10;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":W" + Conversions.ToString((int)(num2 - 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":A" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "STT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 3.2 }, null, null, false, true);
            tasWSheet.get_Range("B" + Conversions.ToString(num2) + ":B" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Phòng ban");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B", Missing.Value], null, "ColumnWidth", new object[] { 14 }, null, null, false, true);
            tasWSheet.get_Range("C" + Conversions.ToString(num2) + ":C" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Mã NV");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["C", Missing.Value], null, "ColumnWidth", new object[] { 10 }, null, null, false, true);
            tasWSheet.get_Range("D" + Conversions.ToString(num2) + ":D" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("D" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tên nhân viên");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["D", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("E" + Conversions.ToString(num2) + ":E" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Chức vụ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["E", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("F" + Conversions.ToString(num2) + ":F" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ngày");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["F", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("G" + Conversions.ToString(num2) + ":G" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("G" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Thứ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["G", Missing.Value], null, "ColumnWidth", new object[] { 10 }, null, null, false, true);
            tasWSheet.get_Range("H" + Conversions.ToString(num2) + ":H" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("H" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Giờ vào");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["H", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("I" + Conversions.ToString(num2) + ":I" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("I" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Giờ ra");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["I", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("J" + Conversions.ToString(num2) + ":K" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Làm việc");
            tasWSheet.get_Range("J" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Giờ Công");
            tasWSheet.get_Range("K" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Ngày Công");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["J:K", Missing.Value], null, "ColumnWidth", new object[] { 5 }, null, null, false, true);
            tasWSheet.get_Range("L" + Conversions.ToString(num2) + ":N" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("L" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tăng ca (Giờ)");
            tasWSheet.get_Range("L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "TCNT");
            tasWSheet.get_Range("M" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "TCCN");
            tasWSheet.get_Range("N" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "TCNL");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["L:N", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("O" + Conversions.ToString(num2) + ":Q" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("O" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Đi trễ, Về sớm (Phút)");
            tasWSheet.get_Range("O" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Trễ");
            tasWSheet.get_Range("P" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Sớm");
            tasWSheet.get_Range("Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Tổng");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["O:Q", Missing.Value], null, "ColumnWidth", new object[] { 4 }, null, null, false, true);
            tasWSheet.get_Range("R" + Conversions.ToString(num2) + ":R" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("R" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Đêm");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["R", Missing.Value], null, "ColumnWidth", new object[] { 4 }, null, null, false, true);
            tasWSheet.get_Range("S" + Conversions.ToString(num2) + ":S" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("S" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "CT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["S", Missing.Value], null, "ColumnWidth", new object[] { 3 }, null, null, false, true);
            tasWSheet.get_Range("T" + Conversions.ToString(num2) + ":T" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("T" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "F");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["T", Missing.Value], null, "ColumnWidth", new object[] { 3 }, null, null, false, true);
            tasWSheet.get_Range("U" + Conversions.ToString(num2) + ":U" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("U" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tổng");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["U", Missing.Value], null, "ColumnWidth", new object[] { 4.5 }, null, null, false, true);
            tasWSheet.get_Range("V" + Conversions.ToString(num2) + ":V" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("V" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ca");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["V", Missing.Value], null, "ColumnWidth", new object[] { 12 }, null, null, false, true);
            tasWSheet.get_Range("W" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("W" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ký hiệu");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["W", Missing.Value], null, "ColumnWidth", new object[] { 10 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Rows["4:5", Missing.Value], null, "RowHeight", new object[] { 0x19 }, null, null, false, true);
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":W" + Conversions.ToString((int)(num2 + 1)), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignCenter;
            tasWSheet = null;

        }
        void InChiTiet()
        {
            TasStartExcel("CHITIETCHAMCONG");
            int count = dt.Rows.Count;
            // doc do phan giai man hinh
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = count;
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            int i = 0;
           
            System.Windows.Forms.Application.DoEvents();
            if (count!=0)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet tasWSheet = TasWSheet;
                    HeaderChiTiet(1, dtpbd.Value.Date, dtpkt.Value.Date);
                    //thuc hien tao border cho dong 
                    foreach (DataRow row in dt.Rows)
                    {
                        f.tocdo.Value = i;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)i) / ((double)count)), 2) * 100.0)) + "%";
                        System.Windows.Forms.Application.DoEvents();
                        f.Activate();
                        tasWSheet.get_Range("A" + Conversions.ToString(i+6) + ":W" + Conversions.ToString((int)(i+6)), Missing.Value).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = 1;
                        tasWSheet.get_Range("A" + Conversions.ToString(i+6) + ":W" + Conversions.ToString((int)(i+6)), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        tasWSheet.get_Range("A" + Conversions.ToString(i+6) + ":W" + Conversions.ToString((int)(i+6)), Missing.Value).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 3;
                        // THUC HIEN XUAT DU LIEU
                        //STT
                        tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + (i + 1).ToString());
                        tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //phong ban
                        tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENPB"].ToString()  );
                        tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //MANV
                        tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["MANV"].ToString());
                        tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //TENNV 
                        tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENNV"].ToString());
                        tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //CHUCVU
                        tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENCV"].ToString());
                        tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //NGAY
                        tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["NGAY"].ToString());
                        tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        //thu
                        tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["THU"].ToString());
                        tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //check in 
                        tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CHECKIN"].ToString());
                        tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //check out
                        tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CHECKOUT"].ToString());
                        tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //gio cong
                        tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["GIOCONG"].ToString());
                        tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //ngay cong
                        tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["NGAYCONG"].ToString());
                        tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //tang ca ngay thuong 
                        tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TCNT"].ToString());
                        tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //tang ca ngay chu nhat 
                        tasWSheet.get_Range("M" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TCCN"].ToString());
                        tasWSheet.get_Range("M" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //tang ca ngay le 
                        tasWSheet.get_Range("N" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TCNL"].ToString());
                        tasWSheet.get_Range("N" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //tinh tre
                        tasWSheet.get_Range("O" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TRE"].ToString());
                        tasWSheet.get_Range("O" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //tinh som
                        tasWSheet.get_Range("P" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["SOM"].ToString());
                        tasWSheet.get_Range("P" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //tong tre som
                        int tong = int.Parse(row["SOM"].ToString()) + int.Parse(row["TRE"].ToString());
                        tasWSheet.get_Range("Q" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tong.ToString());
                        tasWSheet.get_Range("Q" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //dem 
                        tasWSheet.get_Range("R" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["GIODEM"].ToString());
                        tasWSheet.get_Range("R" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //di cong tac
                        tasWSheet.get_Range("S" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CONGTAC"].ToString());
                        tasWSheet.get_Range("S" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //nghi phep
                        tasWSheet.get_Range("T" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["PHEP"].ToString());
                        tasWSheet.get_Range("T" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        float tonggio = float.Parse(row["GIOCONG"].ToString()) + float.Parse(row["TCNT"].ToString()) +float.Parse (row["TCCN"].ToString()) + float.Parse(row["TCNL"].ToString());
                        //tong cong 
                        tasWSheet.get_Range("U" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tonggio.ToString());
                        tasWSheet.get_Range("U" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //ten ca
                        tasWSheet.get_Range("V" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value,row["TENCA"].ToString());
                        tasWSheet.get_Range("V" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                        //cot cuoi cung la ky hieu
                        tasWSheet.get_Range("W" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["KYHIEU"].ToString());
                        i++;
                    }
                    tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":W" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                    FooterChiTiet(i + 6);
            
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Lỗi :" + ex.Message.ToString());
                    f.Close();
                }
                finally
                {
                    f.Close();
                }
            } 
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        public void FooterChiTiet(int dong)
        {
            TasWSheet.get_Range("A" + Conversions.ToString(dong) + ":W" + Conversions.ToString(dong), Missing.Value).Interior.Color = 0xc0c0c0;
            TasWSheet.get_Range("A6:W" + Conversions.ToString(dong), Missing.Value).Borders.LineStyle = 1;
            if (dong > 7)
            {
                TasWSheet.get_Range("A6:W" + Conversions.ToString((int)(dong - 1)), Missing.Value).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = 3;
            }
        }
        #endregion
        #region "Danh sách nhân viên về sớm"
        void InVeSom()
        {
            TasStartExcel("NVVESOM");
            int count = dt.Rows.Count;
            // doc do phan giai man hinh
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = count;
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            int i = 0;
            int j = 0;
            System.Windows.Forms.Application.DoEvents();
            if (dt.Rows.Count != 0)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet tasWSheet = TasWSheet;
                    TaoHeaderSom(1, dtpbd.Value.Date, dtpkt.Value.Date);
                    foreach (DataRow row in dt.Rows)
                    {
                        f.tocdo.Value = j;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)j) / ((double)count)), 2) * 100.0)) + "%";
                        System.Windows.Forms.Application.DoEvents();
                        f.Activate();
                        if (int.Parse(row["SOM"].ToString()) > 0)
                        {
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = 1;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 3;
                            //STT
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + (i + 1).ToString());
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //phong ban
                            tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENPB"].ToString());
                            tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //MANV
                            tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["MANV"].ToString());
                            tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //TENNV 
                            tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENNV"].ToString());
                            tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //CHUCVU
                            tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENCV"].ToString());
                            tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //NGAY
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["NGAY"].ToString());
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            //thu
                            tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["THU"].ToString());
                            tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //check in 
                            tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CHECKIN"].ToString());
                            tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //check out
                            tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CHECKOUT"].ToString());
                            tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //ve som
                            //tinh som
                            tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["SOM"].ToString());
                            tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //ten ca
                            tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENCA"].ToString());
                            tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //cot cuoi cung la ky hieu
                            if (!string.IsNullOrEmpty(row["KYHIEU"].ToString()))
                            {
                                tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["KYHIEU"].ToString());
                            }
                            else
                            {
                                tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "SOM+");
                            }
                            
                            i++;
                            j++;
                        }
                        else
                        {
                            j++;
                            continue;
                        }
                    }
                    tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                    FooterXuatSom(i + 6);
                }
                catch(Exception ex)
                {
                    MessageBox.Show("Lỗi :" + ex.Message.ToString());
                    f.Close();
                }
                finally { f.Close(); }
            }
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        void TaoHeaderSom(int StartRow, DateTime tungay, DateTime denngay)
        {
            int num2 = StartRow + 3;
            Worksheet tasWSheet = TasWSheet;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":L" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).set_Value(Missing.Value, "DANH SÁCH NHÂN VIÊN VỀ SỚM");
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Size = 14;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":L" + Conversions.ToString((int)(num2 - 3)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":L" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).set_Value(Missing.Value, "Từ ngày " + Conversions.ToString(tungay) + " đến ngày " + Conversions.ToString(denngay));
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Size = 10;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":L" + Conversions.ToString((int)(num2 - 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":A" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "STT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 4.2 }, null, null, false, true);
            tasWSheet.get_Range("B" + Conversions.ToString(num2) + ":B" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Phòng ban");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B", Missing.Value], null, "ColumnWidth", new object[] { 14 }, null, null, false, true);
            tasWSheet.get_Range("C" + Conversions.ToString(num2) + ":C" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Mã NV");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["C", Missing.Value], null, "ColumnWidth", new object[] { 18 }, null, null, false, true);
            tasWSheet.get_Range("D" + Conversions.ToString(num2) + ":D" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("D" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tên nhân viên");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["D", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("E" + Conversions.ToString(num2) + ":E" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Chức vụ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["E", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("F" + Conversions.ToString(num2) + ":F" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ngày");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["F", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("G" + Conversions.ToString(num2) + ":G" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("G" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Thứ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["G", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("H" + Conversions.ToString(num2) + ":H" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("H" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Giờ vào");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["H", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("I" + Conversions.ToString(num2) + ":I" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("I" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Giờ ra");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["I", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("J" + Conversions.ToString(num2) + ":J" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "về sớm");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["J", Missing.Value], null, "ColumnWidth", new object[] { 4 }, null, null, false, true);
            tasWSheet.get_Range("K" + Conversions.ToString(num2) + ":K" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("K" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ca");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["K", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("L" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("L" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "K\x00ed hiệu");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["L", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Rows["5", Missing.Value], null, "RowHeight", new object[] { 0x19 }, null, null, false, true);
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignCenter;

        }
        public void FooterXuatSom(int dong)
        {
            TasWSheet.get_Range("A" + Conversions.ToString(dong) + ":L" + Conversions.ToString(dong), Missing.Value).Interior.Color = 0xc0c0c0;
            TasWSheet.get_Range("A6:L" + Conversions.ToString(dong), Missing.Value).Borders.LineStyle = 1;
            if (dong > 7)
            {
                TasWSheet.get_Range("A6:L" + Conversions.ToString((int)(dong - 1)), Missing.Value).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = 3;
            }
        }
        #endregion
        #region "Danh sách nhân viên đi trễ"
        void InDiTre()
        {
            TasStartExcel("NVDITRE");
            int count = dt.Rows.Count;
            // doc do phan giai man hinh
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = count;
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            int i = 0;
            int j = 0;
            System.Windows.Forms.Application.DoEvents();
            if (dt.Rows.Count != 0)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet tasWSheet = TasWSheet;
                    TaoHeaderTre(1, dtpbd.Value.Date, dtpkt.Value.Date);
                    foreach (DataRow row in dt.Rows)
                    {
                        f.tocdo.Value = j;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)i) / ((double)count)), 2) * 100.0)) + "%";
                        System.Windows.Forms.Application.DoEvents();
                        f.Activate();
                        if (int.Parse(row["TRE"].ToString()) > 0)
                        {
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = 1;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 3;
                            //STT
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + (i + 1).ToString());
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //phong ban
                            tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENPB"].ToString());
                            tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //MANV
                            tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["MANV"].ToString());
                            tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //TENNV 
                            tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENNV"].ToString());
                            tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //CHUCVU
                            tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENCV"].ToString());
                            tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //NGAY
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["NGAY"].ToString());
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            //thu
                            tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["THU"].ToString());
                            tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //check in 
                            tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CHECKIN"].ToString());
                            tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //check out
                            tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["CHECKOUT"].ToString());
                            tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //ve som
                            //tinh som
                            tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TRE"].ToString());
                            tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //ten ca
                            tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENCA"].ToString());
                            tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //cot cuoi cung la ky hieu
                            if (!string.IsNullOrEmpty(row["KYHIEU"].ToString()))
                                tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["KYHIEU"].ToString());
                            else
                                tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value,"TRE+");     
                            i++;
                            j++;
                        }
                        else
                        {
                            j++;
                            continue;
                        }
                    }
                    tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":L" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                    FooterXuatSom(i + 6);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Lỗi :" + ex.Message.ToString());
                    f.Close();
                }
                finally { f.Close(); }
            }
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        void TaoHeaderTre(int StartRow, DateTime tungay, DateTime denngay)
        {
            int num2 = StartRow + 3;
            Worksheet tasWSheet = TasWSheet;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":L" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).set_Value(Missing.Value, "DANH SÁCH NHÂN VIÊN ĐI TRỄ");
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Size = 14;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":L" + Conversions.ToString((int)(num2 - 3)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":L" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).set_Value(Missing.Value, "Từ ngày " + Conversions.ToString(tungay) + " đến ngày " + Conversions.ToString(denngay));
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Size = 10;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":L" + Conversions.ToString((int)(num2 - 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":A" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "STT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 4.2 }, null, null, false, true);
            tasWSheet.get_Range("B" + Conversions.ToString(num2) + ":B" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Phòng ban");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B", Missing.Value], null, "ColumnWidth", new object[] { 14 }, null, null, false, true);
            tasWSheet.get_Range("C" + Conversions.ToString(num2) + ":C" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Mã NV");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["C", Missing.Value], null, "ColumnWidth", new object[] { 18 }, null, null, false, true);
            tasWSheet.get_Range("D" + Conversions.ToString(num2) + ":D" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("D" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tên nhân viên");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["D", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("E" + Conversions.ToString(num2) + ":E" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Chức vụ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["E", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("F" + Conversions.ToString(num2) + ":F" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ngày");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["F", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("G" + Conversions.ToString(num2) + ":G" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("G" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Thứ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["G", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("H" + Conversions.ToString(num2) + ":H" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("H" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Giờ vào");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["H", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("I" + Conversions.ToString(num2) + ":I" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("I" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Giờ ra");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["I", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            tasWSheet.get_Range("J" + Conversions.ToString(num2) + ":J" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Đi trễ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["J", Missing.Value], null, "ColumnWidth", new object[] { 4 }, null, null, false, true);
            tasWSheet.get_Range("K" + Conversions.ToString(num2) + ":K" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("K" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ca");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["K", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("L" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("L" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Ký hiệu");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["L", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Rows["5", Missing.Value], null, "RowHeight", new object[] { 0x19 }, null, null, false, true);
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignCenter;

        }
        
        #endregion
        #region "Danh sách nhân viên vắng"
        void InVang()
        {
            TasStartExcel("NVVANG");
            int count = dt.Rows.Count;
            // doc do phan giai man hinh
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = count;
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            int i = 0;
            int j = 0;
            System.Windows.Forms.Application.DoEvents();
            if (dt.Rows.Count!=0)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet tasWSheet = TasWSheet;
                    TaoHeaderVang(1, dtpbd.Value.Date, dtpkt.Value.Date);
                    foreach (DataRow row in dt.Rows)
                    {
                        f.tocdo.Value = j;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)i) / ((double)count)), 2) * 100.0)) + "%";
                        System.Windows.Forms.Application.DoEvents();
                        f.Activate();
                        string kyhieu =row["KYHIEU"].ToString();
                        kyhieu.ToUpper();
                        if (kyhieu.Trim()=="V")
                        {
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":H" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = 1;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":H" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":H" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 3;
                            //STT
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + (i + 1).ToString());
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //phong ban
                            tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENPB"].ToString());
                            tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //MANV
                            tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["MANV"].ToString());
                            tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //TENNV 
                            tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENNV"].ToString());
                            tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //CHUCVU
                            tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["TENCV"].ToString());
                            tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //NGAY
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["NGAY"].ToString());
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            //thu
                            tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["THU"].ToString());
                            tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //cot cuoi cung la ky hieu
                            tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, row["KYHIEU"].ToString());
                            i++;
                            j++;
                        }
                        else
                        {
                            j++;
                            continue;
                        }
                    }
                    tasWSheet.get_Range("A" + Conversions.ToString(i + 6) + ":H" + Conversions.ToString((int)(i + 6)), Missing.Value).Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                    FooterXuatVang(i + 6);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Lỗi :" + ex.Message.ToString());
                    f.Close();
                }
                finally { f.Close(); }
            } 
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        void TaoHeaderVang(int StartRow, DateTime tungay, DateTime denngay)
        {
            int num = StartRow + 3;
            Worksheet tasWSheet = TasWSheet;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 3)) + ":H" + Conversions.ToString((int)(num - 3)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 3)), Missing.Value).set_Value(Missing.Value, "DANH SÁCH NHÂN VIÊN VẮNG");
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 3)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 3)), Missing.Value).Font.Size = 14;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 3)) + ":H" + Conversions.ToString((int)(num - 3)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 2)) + ":H" + Conversions.ToString((int)(num - 2)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 2)), Missing.Value).set_Value(Missing.Value, "Từ ng\x00e0y " + Conversions.ToString(tungay) + " đến ng\x00e0y " + Conversions.ToString(denngay));
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 2)), Missing.Value).Font.Size = 10;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 2)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num - 2)) + ":H" + Conversions.ToString((int)(num - 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":A" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "STT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 4.2 }, null, null, false, true);
            tasWSheet.get_Range("B" + Conversions.ToString(num) + ":B" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Ph\x00f2ng ban");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B", Missing.Value], null, "ColumnWidth", new object[] { 14 }, null, null, false, true);
            tasWSheet.get_Range("C" + Conversions.ToString(num) + ":C" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "M\x00e3 NV");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["C", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("D" + Conversions.ToString(num) + ":D" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("D" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "T\x00ean nh\x00e2n vi\x00ean");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["D", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("E" + Conversions.ToString(num) + ":E" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Chức vụ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["E", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("F" + Conversions.ToString(num) + ":F" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Ng\x00e0y");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["F", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("G" + Conversions.ToString(num) + ":G" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("G" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Thứ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["G", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("H" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("H" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "K\x00ed hiệu");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["H", Missing.Value], null, "ColumnWidth", new object[] { 5.2 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Rows["5", Missing.Value], null, "RowHeight", new object[] { 0x19 }, null, null, false, true);
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":H" + Conversions.ToString((int)(num + 1)), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignCenter;
            tasWSheet = null;

        }
        public void FooterXuatVang(int dong)
        {
            TasWSheet.get_Range("A" + Conversions.ToString(dong) + ":H" + Conversions.ToString(dong), Missing.Value).Interior.Color = 0xc0c0c0;
            TasWSheet.get_Range("A6:H" + Conversions.ToString(dong), Missing.Value).Borders.LineStyle = 1;
            if (dong > 7)
            {
                TasWSheet.get_Range("A6:H" + Conversions.ToString((int)(dong - 1)), Missing.Value).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = 3;
            }
        }
        #endregion
        #region "In chi tiết chấm công từng nhân viên "
        public void TaoHeaderDetail(long StartRow, string StrInfo,_Worksheet shett)
        {
            long num = StartRow + 3;
            _Worksheet tasWSheet = shett;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 2L)) + ":P" + Conversions.ToString((long)(num - 2L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 2L)) + ":P" + Conversions.ToString((long)(num - 2L)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 2L)), Missing.Value).set_Value(Missing.Value, "BẢNG CHI TIẾT CHẤM CÔNG");
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 2L)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 2L)), Missing.Value).Font.Size = 0x10;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 2L)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 1L)) + ":P" + Conversions.ToString((long)(num - 1L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 1L)) + ":P" + Conversions.ToString((long)(num - 1L)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 1L)), Missing.Value).set_Value(Missing.Value, StrInfo);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 1L)), Missing.Value).Font.Bold = true; ;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 1L)), Missing.Value).Font.Size = 12;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num - 1L)), Missing.Value).Interior.Color = ColorTranslator.ToWin32(Color.Yellow);
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":B" + Conversions.ToString(num), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Ngày thường");
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 1L)) + ":B" + Conversions.ToString((long)(num + 1L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 1L)), Missing.Value).set_Value(Missing.Value, "Chủ Nhật");
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 2L)) + ":B" + Conversions.ToString((long)(num + 2L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 2L)), Missing.Value).set_Value(Missing.Value, "Lễ");
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":D" + Conversions.ToString((long)(num + 2L)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":D" + Conversions.ToString((long)(num + 2L)), Missing.Value).Font.Italic = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num) + ":D" + Conversions.ToString((long)(num + 2L)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("F" + Conversions.ToString(num) + ":G" + Conversions.ToString(num), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Số lần trễ");
            tasWSheet.get_Range("F" + Conversions.ToString((long)(num + 1L)) + ":G" + Conversions.ToString((long)(num + 1L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString((long)(num + 1L)), Missing.Value).set_Value(Missing.Value, "Số lần sớm");
            tasWSheet.get_Range("F" + Conversions.ToString((long)(num + 2L)) + ":G" + Conversions.ToString((long)(num + 2L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString((long)(num + 2L)), Missing.Value).set_Value(Missing.Value, "Vắng KP");
            tasWSheet.get_Range("F" + Conversions.ToString(num) + ":H" + Conversions.ToString((long)(num + 2L)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("F" + Conversions.ToString(num) + ":H" + Conversions.ToString((long)(num + 2L)), Missing.Value).Font.Italic = true;
            tasWSheet.get_Range("F" + Conversions.ToString(num) + ":H" + Conversions.ToString((long)(num + 2L)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("J" + Conversions.ToString(num) + ":K" + Conversions.ToString(num), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString(num), Missing.Value).set_Value(Missing.Value, "Số giờ trễ");
            tasWSheet.get_Range("J" + Conversions.ToString((long)(num + 1L)), Missing.Value).set_Value(Missing.Value, "Số giờ sớm");
            tasWSheet.get_Range("J" + Conversions.ToString((long)(num + 2L)) + ":K" + Conversions.ToString((long)(num + 2L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString((long)(num + 2L)), Missing.Value).set_Value(Missing.Value, "Vắng CP");
            tasWSheet.get_Range("L" + Conversions.ToString(num) + ":M" + Conversions.ToString(num), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("L" + Conversions.ToString((long)(num + 1L)) + ":M" + Conversions.ToString((long)(num + 1L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("L" + Conversions.ToString((long)(num + 2L)) + ":M" + Conversions.ToString((long)(num + 2L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("N" + Conversions.ToString(num) + ":P" + Conversions.ToString((long)(num + 2L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString(num) + ":M" + Conversions.ToString((long)(num + 2L)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("J" + Conversions.ToString(num) + ":M" + Conversions.ToString((long)(num + 2L)), Missing.Value).Font.Italic = true;
            tasWSheet.get_Range("J" + Conversions.ToString(num) + ":P" + Conversions.ToString((long)(num + 2L)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 3L)), Missing.Value).set_Value(Missing.Value, "Chi tiết");
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 3L)), Missing.Value).Font.Italic = true;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 3L)) + ":P" + Conversions.ToString((long)(num + 3L)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 3L)) + ":P" + Conversions.ToString((long)(num + 3L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 4L)) + ":A" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "Ngày");
            tasWSheet.get_Range("B" + Conversions.ToString((long)(num + 4L)) + ":B" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "Thứ");
            tasWSheet.get_Range("C" + Conversions.ToString((long)(num + 4L)), "D" + Conversions.ToString((long)(num + 4L))).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, 1);
            tasWSheet.get_Range("C" + Conversions.ToString((long)(num + 5L)), Missing.Value).set_Value(Missing.Value, "Vào");
            tasWSheet.get_Range("D" + Conversions.ToString((long)(num + 5L)), Missing.Value).set_Value(Missing.Value, "Ra");
            tasWSheet.get_Range("E" + Conversions.ToString((long)(num + 4L)), "F" + Conversions.ToString((long)(num + 4L))).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, 2);
            tasWSheet.get_Range("E" + Conversions.ToString((long)(num + 5L)), Missing.Value).set_Value(Missing.Value, "Vào");
            tasWSheet.get_Range("F" + Conversions.ToString((long)(num + 5L)), Missing.Value).set_Value(Missing.Value, "Ra");
            tasWSheet.get_Range("G" + Conversions.ToString((long)(num + 4L)), "H" + Conversions.ToString((long)(num + 4L))).Merge(Missing.Value);
            tasWSheet.get_Range("G" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, 3);
            tasWSheet.get_Range("G" + Conversions.ToString((long)(num + 5L)), Missing.Value).set_Value(Missing.Value, "Vào");
            tasWSheet.get_Range("H" + Conversions.ToString((long)(num + 5L)), Missing.Value).set_Value(Missing.Value, "Ra");
            tasWSheet.get_Range("I" + Conversions.ToString((long)(num + 4L)) + ":I" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("I" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "Trễ");
            tasWSheet.get_Range("J" + Conversions.ToString((long)(num + 4L)) + ":J" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("J" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "Sớm");
            tasWSheet.get_Range("K" + Conversions.ToString((long)(num + 4L)) + ":K" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("K" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "T.Giờ");
            tasWSheet.get_Range("L" + Conversions.ToString((long)(num + 4L)) + ":L" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("L" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "Công");
            tasWSheet.get_Range("M" + Conversions.ToString((long)(num + 4L)) + ":M" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("M" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "TCCN");
            tasWSheet.get_Range("N" + Conversions.ToString((long)(num + 4L)) + ":N" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("N" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "TCNT");
            tasWSheet.get_Range("O" + Conversions.ToString((long)(num + 4L)) + ":O" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("O" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "TCNL");
            tasWSheet.get_Range("P" + Conversions.ToString((long)(num + 4L)) + ":P" + Conversions.ToString((long)(num + 5L)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("P" + Conversions.ToString((long)(num + 4L)), Missing.Value).set_Value(Missing.Value, "Ký hiệu");
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 4L)) + ":P" + Conversions.ToString((long)(num + 5L)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 4L)) + ":P" + Conversions.ToString((long)(num + 5L)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((long)(num + 4L)) + ":P" + Conversions.ToString((long)(num + 5L)), Missing.Value).Borders.LineStyle = 1;
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B:P", Missing.Value], null, "ColumnWidth", new object[] { 10 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet = null;
        }
        void InDetailNV()
        {
            ExApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook  Workbookss= ExApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Sheets sheet = Workbookss.Worksheets;
            Excel._Worksheet Ws;
            ExApp.Visible = true;
            long count = dt.Rows.Count;
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = int.Parse(count.ToString());
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            System.Windows.Forms.Application.DoEvents();
            int chisodong = 0;
            // tinh toan sem co bao nhieu nhan vien 
            int phantram = 1;
            long songay = DateAndTime.DateDiff(DateInterval.Day,dtpbd.Value,dtpkt.Value,FirstDayOfWeek.System,FirstWeekOfYear.System)+1;
            if (dt.Rows.Count != 0)
            {
                try
                {
                    long sonv = count / songay;
                    for (int i = 1; i <= sonv;i++ )
                    {
                        f.tocdo.Value = phantram;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)i) / ((double)sonv)), 2) * 100.0)) + "%";
                        System.Windows.Forms.Application.DoEvents();
                        f.Activate();
                        Workbookss.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                        Ws = (Excel._Worksheet)sheet.get_Item(i);
                        Ws = (Excel.Worksheet)Workbookss.ActiveSheet;
                        // thuc hien bo du lieu 
                        string namesheet ="";
                        string info = "";
                        // info 
                        int songaythuong=0;
                        float giongaythuong = 0;
                        int songaychunhat = 0;
                        float giongaychunhat = 0;
                        int songayle = 0;
                        float giongayle = 0;
                        int solantre = 0;
                        float sogiotre=0;
                        int solansom = 0;
                        float sogiosom = 0;
                        int solanvangcophep = 0;//la co dang ky nghi phep
                        int solanvangkhongphep = 0;//vang ma khong dang ky nghi phep
                        int j = 0;
                        string hoten = "";
                        _Worksheet WSDetail = Ws;
                        //thuc hien vong lap
                        //phai in truoc sau do tao header sau
                        for (j = 0; j <songay;j++ )
                        {
                            //duyet theo tung dong
                            DataRow row = dt.Rows[chisodong];
                            float tonggio = float.Parse(row["GIOCONG"].ToString()) + float.Parse(row["TCNT"].ToString()) + float.Parse(row["TCCN"].ToString()) + float.Parse(row["TCNL"].ToString());
                            namesheet = row["MANV"].ToString();
                            hoten = row["TENNV"].ToString();
                            info = "Mã nhân viên : " + row["MANV"].ToString() + "     " + "Tên nhân viên : " + row["TENNV"].ToString() + "     " + "Bộ phận : " + row["TENPB"].ToString();
                            // tinh so lan tre somg
                            if (int.Parse(row["TRE"].ToString()) > 0)
                            {
                                solantre++;
                                sogiotre += float.Parse(row["TRE"].ToString());
                            }
                            if (int.Parse(row["SOM"].ToString()) > 0)
                            {
                                solansom++;
                                sogiosom += float.Parse(row["SOM"].ToString());
                            }
                            if (row["KYHIEU"].ToString()=="V"&&row["THU"].ToString()!="CN")
                            {
                                solanvangkhongphep++;
                            }
                            if (row["KYHIEU"].ToString() == "CT" || row["KYHIEU"].ToString() == "NP"||row["KYHIEU"].ToString()=="NL"||(row["THU"].ToString()=="CN"&& row["KYHIEU"].ToString()=="V"))
                            {
                                solanvangcophep++;
                            }
                            if (float.Parse(row["TCCN"].ToString()) > 0 || row["THU"].ToString() == "CN")
                            {
                                songaychunhat++;
                                giongaychunhat += tonggio;
                            }
                            if (float.Parse(row["TCNL"].ToString()) > 0||row["KYHIEU"].ToString()=="NL")
                            {
                                songayle++;
                                giongayle += tonggio;
                                
                            }
                            if (row["THU"].ToString() != "CN" && row["KYHIEU"].ToString() != "V" &&row["THU"].ToString()!="NL")
                            {
                                songaythuong++;
                                giongaythuong += tonggio;
                            }
                            //bat dau ghi 
                            WSDetail.get_Range("A" + Conversions.ToString(j + 10) + ":P" + Conversions.ToString((int)(j + 10)), Missing.Value).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = 1;
                            WSDetail.get_Range("A" + Conversions.ToString(j + 10) + ":P" + Conversions.ToString((int)(j + 10)), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            WSDetail.get_Range("A" + Conversions.ToString(j + 10) + ":P" + Conversions.ToString((int)(j + 10)), Missing.Value).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 3;
                            //thuc hien in nhan vien
                            //NGAY
                            WSDetail.get_Range("A" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["NGAY"].ToString());
                            WSDetail.get_Range("A" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            WSDetail.get_Range("A" + Conversions.ToString(j + 10), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                            //thu
                            WSDetail.get_Range("B" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["THU"].ToString());
                            WSDetail.get_Range("B" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //check in 
                            WSDetail.get_Range("C" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["CHECKIN"].ToString());
                            WSDetail.get_Range("C" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //check out
                            WSDetail.get_Range("D" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["CHECKOUT"].ToString());
                            WSDetail.get_Range("D" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //vao 2 
                            WSDetail.get_Range("E" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //ra 2 
                            WSDetail.get_Range("F" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //vao 3
                            WSDetail.get_Range("G" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //ra 3
                            WSDetail.get_Range("H" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //tinh tre
                            WSDetail.get_Range("I" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["TRE"].ToString());
                            WSDetail.get_Range("I" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //tinh som
                            WSDetail.get_Range("J" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["SOM"].ToString());
                            WSDetail.get_Range("J" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //tong gio
                           
                            WSDetail.get_Range("K" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, tonggio.ToString());
                            WSDetail.get_Range("K" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            // cong\
                            //ngay cong
                            WSDetail.get_Range("L" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["NGAYCONG"].ToString());
                            WSDetail.get_Range("L" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //tang ca ngay thuong 
                            WSDetail.get_Range("M" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["TCCN"].ToString());
                            WSDetail.get_Range("M" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //tang ca ngay chu nhat 
                            WSDetail.get_Range("N" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["TCNT"].ToString());
                            WSDetail.get_Range("N" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //tang ca ngay le 
                            WSDetail.get_Range("O" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["TCNL"].ToString());
                            WSDetail.get_Range("O" + Conversions.ToString(j + 10), Missing.Value).Borders[XlBordersIndex.xlEdgeRight].LineStyle = 1;
                            //cot cuoi cung la ky hieu
                            WSDetail.get_Range("P" + Conversions.ToString(j + 10), Missing.Value).set_Value(Missing.Value, row["KYHIEU"].ToString());
                            chisodong++;
                            phantram++;
                        }
                        WSDetail.get_Range("A" + Conversions.ToString(j + 10) + ":P" + Conversions.ToString((int)(j + 10)), Missing.Value).Borders[XlBordersIndex.xlEdgeTop].LineStyle = 1;
                        Ws.Name = namesheet;
                        TaoHeaderDetail(1, info, Ws);
                        //thuc hien ghi truc tiep vao o
                        //so lan tre
                        WSDetail.get_Range("H4", Missing.Value).set_Value(Missing.Value, solantre.ToString());
                        //so lan som
                        WSDetail.get_Range("H5", Missing.Value).set_Value(Missing.Value, solansom.ToString());
                        //vang khong phep
                        WSDetail.get_Range("H6", Missing.Value).set_Value(Missing.Value, solanvangkhongphep.ToString());
                        //vang co phep
                        WSDetail.get_Range("L6", Missing.Value).set_Value(Missing.Value, solanvangcophep.ToString());
                        // so gio tre 
                        WSDetail.get_Range("L4", Missing.Value).set_Value(Missing.Value, (sogiotre/60).ToString());
                        //so gio som 
                        WSDetail.get_Range("L5", Missing.Value).set_Value(Missing.Value, (sogiosom/60).ToString());
                        //tinh la ngay thuong 
                        WSDetail.get_Range("C4", Missing.Value).set_Value(Missing.Value, songaythuong.ToString());
                        WSDetail.get_Range("D4", Missing.Value).set_Value(Missing.Value, giongaythuong.ToString());
                        //tinh la chu nhat 
                        WSDetail.get_Range("C5", Missing.Value).set_Value(Missing.Value, songaychunhat.ToString());
                        WSDetail.get_Range("D5", Missing.Value).set_Value(Missing.Value, giongaychunhat.ToString());
                        //tinh la ngay le 
                        WSDetail.get_Range("C6", Missing.Value).set_Value(Missing.Value, songayle.ToString());
                        WSDetail.get_Range("D6", Missing.Value).set_Value(Missing.Value, giongayle.ToString());
                        //in footer 
                        hoten.ToUpper();
                        string ngayhientai = "Ngày " + DateTime.Now.Day.ToString() + " Tháng " + DateTime.Now.Month.ToString() + " Năm " + DateTime.Now.Year;
                        WSDetail.get_Range("N" + (j + 12).ToString() + ":P" + Conversions.ToString(j + 12), Missing.Value).Merge(Missing.Value);
                        WSDetail.get_Range("N" + Conversions.ToString(j + 12), Missing.Value).set_Value(Missing.Value, ngayhientai);
                        WSDetail.get_Range("N" + Conversions.ToString((long)(j + 12)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        WSDetail.get_Range("N" + (j + 13).ToString() + ":P" + Conversions.ToString(j + 13), Missing.Value).Merge(Missing.Value);
                        WSDetail.get_Range("N" + Conversions.ToString(j + 13), Missing.Value).set_Value(Missing.Value, "Ký tên");
                        WSDetail.get_Range("N" + Conversions.ToString((long)(j + 13)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        WSDetail.get_Range("N" + (j + 17).ToString() + ":P" + Conversions.ToString(j + 17), Missing.Value).Merge(Missing.Value);
                        WSDetail.get_Range("N" + Conversions.ToString(j + 17), Missing.Value).set_Value(Missing.Value, hoten);
                        WSDetail.get_Range("N" + Conversions.ToString((long)(j + 17)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        WSDetail.get_Range("N" + Conversions.ToString((long)(j + 17)), Missing.Value).Font.Size = 10;

                    }
                }
                catch(Exception ex)
                {

                    MessageBox.Show("Lỗi :"+ex.Message.ToString());
                    f.Close();
                }
                finally
                {
                    f.Close();
                }
                
               
            } 
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        #endregion
        #region "In Bảng chấm công theo tháng"
        public void HeaderThongKe(int StartRow, DateTime Ngay)
        {
            int num = 0;
            int num7 = 0;
            int num2 = StartRow + 3;
            string str = Strings.Format(DateAndTime.Month(Ngay), "00");
            int num3 = DateAndTime.Year(Ngay);
            Worksheet tasWSheet = TasWSheet;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":AT" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":AT" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).set_Value(Missing.Value, "BẢNG CHẤM CÔNG THÁNG" + str + " NĂM " + Conversions.ToString(num3));
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Size = 14;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)) + ":AL" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Ngày công");
            tasWSheet.get_Range("AM" + Conversions.ToString((int)(num2 - 1)) + ":AM" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("AM" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Chủ Nhật");
            tasWSheet.get_Range("AN" + Conversions.ToString((int)(num2 - 1)) + ":AP" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("AN" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Tăng ca (giờ công)");
            tasWSheet.get_Range("AN" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "NT");
            tasWSheet.get_Range("AP" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "CN");
            tasWSheet.get_Range("AO" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "NL");
            tasWSheet.get_Range("AQ" + Conversions.ToString((int)(num2 - 1)) + ":AR" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("AQ" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Vắng");
            tasWSheet.get_Range("AQ" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "CP");
            tasWSheet.get_Range("AR" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "KP");
            tasWSheet.get_Range("AS" + Conversions.ToString((int)(num2 - 1)) + ":AS" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("AS" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Nghỉ hưởng lương");
            tasWSheet.get_Range("AT" + Conversions.ToString((int)(num2 - 1)) + ":AT" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("AT" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "K\x00ed t\x00ean");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["AL:AM", Missing.Value], null, "ColumnWidth", new object[] { 4.5 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Columns["AN:AS", Missing.Value], null, "ColumnWidth", new object[] { 5.5 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Columns["AS:AT", Missing.Value], null, "ColumnWidth", new object[] { 9 }, null, null, false, true);
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)) + ":AT" + Conversions.ToString(num2), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)) + ":AT" + Conversions.ToString(num2), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)) + ":AT" + Conversions.ToString(num2), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)) + ":AT" + Conversions.ToString(num2), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("AL" + Conversions.ToString((int)(num2 - 1)) + ":AT" + Conversions.ToString(num2), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":A" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "STT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 4.2 }, null, null, false, true);
            tasWSheet.get_Range("B" + Conversions.ToString(num2) + ":B" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Phòng ban");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("C" + Conversions.ToString(num2) + ":C" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Mã NV");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["C", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("D" + Conversions.ToString(num2) + ":D" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("D" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Tên nhân viên");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["D", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("E" + Conversions.ToString(num2) + ":E" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString((int)(num2 - 1)), Missing.Value).set_Value(Missing.Value, "Chức vụ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["E", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("F" + Conversions.ToString(num2) + ":F" + Conversions.ToString((int)(num2 - 1)), Missing.Value).Merge(Missing.Value);
            NewLateBinding.LateSetComplex(tasWSheet.Columns["F", Missing.Value], null, "ColumnWidth", new object[] { 2.7 }, null, null, false, true);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 1)) + ":F" + Conversions.ToString(num2), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 1)) + ":F" + Conversions.ToString(num2), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 1)) + ":F" + Conversions.ToString(num2), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 1)) + ":F" + Conversions.ToString(num2), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 1)) + ":F" + Conversions.ToString(num2), Missing.Value).Borders.LineStyle = 1;
            int num4 = DateAndTime.Day(Ngay);
            for (int i = 0; i <= 30; i++)
            {
                num7 = 60;
                num++;
                tasWSheet.Cells[num2 - 1, num + 6] = DateAndTime.Day(Ngay);
                tasWSheet.Cells[num2, num + 6] = QUANLYCHAMCONG.BL.BUS.Import.Until.ThuVT(DateAndTime.Weekday(Ngay, FirstDayOfWeek.Sunday));
                Ngay = DateAndTime.DateAdd("d", 1.0, Ngay);
                if (num == 1)
                {
                    NewLateBinding.LateSetComplex(tasWSheet.Columns["G:AK", Missing.Value], null, "ColumnWidth", new object[] { 3.8 }, null, null, false, true);
                }
                num4++;
            }
            tasWSheet.get_Range("G" + Conversions.ToString((int)(num2 - 1)) + ":AK" + Conversions.ToString(num2), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("G" + Conversions.ToString((int)(num2 - 1)) + ":AK" + Conversions.ToString(num2), Missing.Value).Font.Size = 10;
            tasWSheet.get_Range("G" + Conversions.ToString((int)(num2 - 1)) + ":AK" + Conversions.ToString(num2), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("G" + Conversions.ToString((int)(num2 - 1)) + ":AK" + Conversions.ToString(num2), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("G" + Conversions.ToString((int)(num2 - 1)) + ":AK" + Conversions.ToString(num2), Missing.Value).Borders.LineStyle = 1;
            NewLateBinding.LateSetComplex(tasWSheet.Rows["3:3", Missing.Value], null, "RowHeight", new object[] { 30 }, null, null, false, true);
            tasWSheet = null;
        }
        public void MergerCell(long dong)
        {
            try
            {
                Worksheet tasWSheet = TasWSheet;
                tasWSheet.get_Range("A" + Conversions.ToString(dong) + ":A" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("B" + Conversions.ToString(dong) + ":B" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("C" + Conversions.ToString(dong) + ":C" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("D" + Conversions.ToString(dong) + ":D" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("E" + Conversions.ToString(dong) + ":E" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AL" + Conversions.ToString(dong) + ":AL" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AM" + Conversions.ToString(dong) + ":AM" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AN" + Conversions.ToString(dong) + ":AN" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AO" + Conversions.ToString(dong) + ":AO" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AP" + Conversions.ToString(dong) + ":AP" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AQ" + Conversions.ToString(dong) + ":AQ" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AR" + Conversions.ToString(dong) + ":AR" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AS" + Conversions.ToString(dong) + ":AS" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("AT" + Conversions.ToString(dong) + ":AT" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Merge(Missing.Value);
                tasWSheet.get_Range("A" + Conversions.ToString(dong) + ":AT" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Font.Size = 8;
                tasWSheet.get_Range("G" + Conversions.ToString(dong) + ":AT" + Conversions.ToString((long)(dong + 1L)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                tasWSheet.get_Range("A" + Conversions.ToString(dong) + ":AT" + Conversions.ToString((long)(dong + 1L)), Missing.Value).WrapText = 1;
                tasWSheet.get_Range("A" + Conversions.ToString(dong) + ":AT" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Borders.LineStyle = 1;
                tasWSheet.get_Range("F" + Conversions.ToString(dong) + ":AK" + Conversions.ToString((long)(dong + 1L)), Missing.Value).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = 3;
                tasWSheet.get_Range("F" + Conversions.ToString(dong), Missing.Value).set_Value(Missing.Value, "NC");
                tasWSheet.get_Range("F" + Conversions.ToString((long)(dong + 1L)), Missing.Value).set_Value(Missing.Value, "TC");
                NewLateBinding.LateSetComplex(tasWSheet.Rows[Conversions.ToString(dong) + ":" + Conversions.ToString((long)(dong + 2L)), Missing.Value], null, "RowHeight", new object[] { 12 }, null, null, false, true);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi :"+ex.Message.ToString());
            }
        }
        void ChamcongThang()
        {
            TasStartExcel("CHAMCONGTHANG");
            int count = dt.Rows.Count;
            // doc do phan giai man hinh
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = count;
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            int i = 0;
            int j = 0;
            long dong = 5;
            int chisodong=0;
            int chisotemp = 0;
            int phantram = 1;
            System.Windows.Forms.Application.DoEvents();
            if (dt.Rows.Count != 0)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet tasWSheet = TasWSheet;
                    HeaderThongKe(1, dtpbd.Value.Date);
                    //tinh so dong = chinh so nhan vien 
                    long songay = DateAndTime.DateDiff(DateInterval.Day, dtpbd.Value, dtpkt.Value, FirstDayOfWeek.System, FirstWeekOfYear.System) + 1;
                    long sodong = count / songay;
                    for (i = 0; i < sodong;i++ )
                    {
                        f.tocdo.Value = phantram;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)i) / ((double)sodong)), 2) * 100.0)) + "%";
                        f.Activate();
                        System.Windows.Forms.Application.DoEvents();
                       
                        chisotemp = 0;
                        float ngaycong = 0;
                        float chunhat = 0;
                        float tangcangaythuong = 0;
                        float tangcangayle = 0;
                        float tangcangaychunhat = 0;
                        int songayphep =0;
                        int songaykhongphep = 0;
                        int nghihuongluong = 0;
                        MergerCell(dong);
                        DataRow row = dt.Rows[chisodong];
                        tasWSheet.get_Range("A" + Conversions.ToString(dong), Missing.Value).set_Value(Missing.Value, (i + 1).ToString());
                        tasWSheet.get_Range("A" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + (i + 1).ToString());
                        tasWSheet.get_Range("B" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + row["TENPB"].ToString());
                        tasWSheet.get_Range("C" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + row["MANV"].ToString());
                        tasWSheet.get_Range("D" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + row["TENNV"].ToString());
                        tasWSheet.get_Range("E" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + row["TENCV"].ToString());
                        while (chisotemp < songay)
                        {
                            DataRow dr = dt.Rows[chisodong];
                            float tangca = float.Parse(dr["TCCN"].ToString()) + float.Parse(dr["TCNL"].ToString()) + float.Parse(dr["TCNT"].ToString());
                           // float tonggio = float.Parse(dr["GIOCONG"].ToString()) + float.Parse(dr["TCNT"].ToString()) + float.Parse(dr["TCCN"].ToString()) + float.Parse(row["TCNL"].ToString());
                            tasWSheet.Cells[dong+1, chisotemp + 7] = tangca.ToString();
                            tasWSheet.Cells[dong, chisotemp + 7] = dr["KYHIEU"].ToString();
                             int n=dr["KYHIEU"].ToString().IndexOf("TC:NT");
                            if (dr["THU"].ToString() != "CN" && dr["KYHIEU"].ToString() != "V" && dr["KYHIEU"].ToString() != "KR" && dr["KYHIEU"].ToString() != "NL" && dr["KYHIEU"].ToString() != "NP" )
                            {
                                ngaycong += float.Parse(dr["NGAYCONG"].ToString());
                            }
                            if(n!=-1)
                            {
                                tangcangaythuong += float.Parse(dr["TCNT"].ToString());
                            }
                            if (dr["THU"].ToString()=="CN"||float.Parse(dr["TCCN"].ToString())>0)
                            {
                                chunhat +=float.Parse( dr["NGAYCONG"].ToString());
                                tangcangaychunhat += float.Parse(dr["TCCN"].ToString()) + float.Parse(dr["GIOCONG"].ToString());
                            }
                            if (float.Parse(dr["TCNL"].ToString()) > 0 || dr["KYHIEU"].ToString().IndexOf("TC:NL") !=-1)
                            {
                                tangcangayle += float.Parse(dr["TCNL"].ToString()) + float.Parse(dr["GIOCONG"].ToString());
                            }
                            //kiem tra so ngay co phep va ngay khong phep
                            if (dr["KYHIEU"].ToString() == "CT" || dr["KYHIEU"].ToString() == "NP" || dr["KYHIEU"].ToString() == "NL")
                            {
                                songayphep++;
                            }
                            if(dr["KYHIEU"].ToString()=="V")
                            {
                                songaykhongphep++;
                            }
                            if (dr["KYHIEU"].ToString() == "NP" || dr["KYHIEU"].ToString() == "NL")
                            {
                                nghihuongluong++;
                            }
                            //thu hien in ra 
                            //tinh so ngay ngi co luon gg
                            chisodong++;
                            chisotemp++;
                            phantram++;
                        }
                        tasWSheet.get_Range("AL" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + ngaycong.ToString());
                        tasWSheet.get_Range("AM" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + chunhat.ToString());
                        tasWSheet.get_Range("AN" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + tangcangaythuong.ToString());
                        tasWSheet.get_Range("AO" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + tangcangayle.ToString());
                        tasWSheet.get_Range("AP" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + tangcangaychunhat.ToString());
                        tasWSheet.get_Range("AQ" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + songayphep.ToString());
                        tasWSheet.get_Range("AR" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + songaykhongphep.ToString());
                        tasWSheet.get_Range("AS" + Conversions.ToString(j + 5), Missing.Value).set_Value(Missing.Value, "'" + nghihuongluong.ToString());
                        dong += 2;
                        j+=2;
                        
                    }
                    //tao foorter 
                    object obj = dong;
                    int so = int.Parse(obj.ToString());
                    FoolterTonghop(so);
                }
                catch (Exception ex)
                {

                    MessageBox.Show("Lỗi :" + ex.Message.ToString());
                    f.Close();
                }
                finally
                {
                    f.Close();
                }
            }
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        public void FoolterTonghop(int dong)
        {
            TasWSheet.get_Range("A" + Conversions.ToString(dong) + ":AT" + Conversions.ToString(dong), Missing.Value).Interior.Color = 0xc0c0c0;
            TasWSheet.get_Range("B" + Conversions.ToString((int)(dong + 2)) + ":C" + Conversions.ToString((int)(dong + 2)), Missing.Value).Merge(Missing.Value);
            TasWSheet.get_Range("A" + Conversions.ToString(dong) + ":AT" + Conversions.ToString(dong), Missing.Value).Borders.LineStyle = 1;
            TasWSheet.get_Range("B" + Conversions.ToString((int)(dong + 2)), Missing.Value).set_Value(Missing.Value, "Trưởng bộ phận");
            TasWSheet.get_Range("AG" + Conversions.ToString((int)(dong + 2)) + ":AL" + Conversions.ToString((int)(dong + 2)), Missing.Value).Merge(Missing.Value);
            TasWSheet.get_Range("AG" + Conversions.ToString((int)(dong + 2)), Missing.Value).set_Value(Missing.Value, "Người lập biểu");
            TasWSheet.get_Range("A" + Conversions.ToString((int)(dong + 2)) + ":AT" + Conversions.ToString((int)(dong + 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            TasWSheet = null;
        }
        #endregion
        #region "In tổng công nhân viên"
        public void TaoHeaderTongCong(int StartRow, DateTime TuNgay, DateTime DenNgay)
        {
            int num2 = StartRow + 3;
            Worksheet tasWSheet = TasWSheet;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)) + ":Q" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).set_Value(Missing.Value, "BẢNG TỔNG HỢP CHẤM CÔNG");
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).Font.Size = 14;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 3)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)) + ":Q" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).set_Value(Missing.Value, "Từ ngày " + Conversions.ToString(TuNgay) + " đến ngày " + Conversions.ToString(DenNgay));
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Size = 10;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString((int)(num2 - 2)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":A" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("A" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "STT");
            tasWSheet.get_Range("A" + Conversions.ToString(num2), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            NewLateBinding.LateSetComplex(tasWSheet.Columns["A", Missing.Value], null, "ColumnWidth", new object[] { 4.2 }, null, null, false, true);
            tasWSheet.get_Range("B" + Conversions.ToString(num2) + ":B" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("B" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Phòng ban");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["B", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("C" + Conversions.ToString(num2) + ":C" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("C" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Mã NV");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["C", Missing.Value], null, "ColumnWidth", new object[] { 15 }, null, null, false, true);
            tasWSheet.get_Range("D" + Conversions.ToString(num2) + ":D" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("D" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tên nhân viên");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["D", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("E" + Conversions.ToString(num2) + ":E" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("E" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Chức vụ");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["E", Missing.Value], null, "ColumnWidth", new object[] { 20 }, null, null, false, true);
            tasWSheet.get_Range("F" + Conversions.ToString(num2) + ":G" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("F" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Làm việc");
            tasWSheet.get_Range("F" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Giờ Công");
            tasWSheet.get_Range("G" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Ngày Công");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["F:G", Missing.Value], null, "ColumnWidth", new object[] { 5 }, null, null, false, true);
            tasWSheet.get_Range("H" + Conversions.ToString(num2) + ":J" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("H" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tăng ca (Giờ)");
            tasWSheet.get_Range("H" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "TCNT");
            tasWSheet.get_Range("I" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "TCCN");
            tasWSheet.get_Range("J" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "TCNL");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["H:J", Missing.Value], null, "ColumnWidth", new object[] { 6 }, null, null, false, true);
            tasWSheet.get_Range("K" + Conversions.ToString(num2) + ":M" + Conversions.ToString(num2), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("K" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Đi trễ, Về sớm (Phút)");
            tasWSheet.get_Range("K" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Trễ");
            tasWSheet.get_Range("L" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Sớm");
            tasWSheet.get_Range("M" + Conversions.ToString((int)(num2 + 1)), Missing.Value).set_Value(Missing.Value, "Tổng");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["K:M", Missing.Value], null, "ColumnWidth", new object[] { 4 }, null, null, false, true);
            tasWSheet.get_Range("N" + Conversions.ToString(num2) + ":N" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("N" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Đêm");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["N", Missing.Value], null, "ColumnWidth", new object[] { 4 }, null, null, false, true);
            tasWSheet.get_Range("O" + Conversions.ToString(num2) + ":O" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("O" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "CT");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["O", Missing.Value], null, "ColumnWidth", new object[] { 3 }, null, null, false, true);
            tasWSheet.get_Range("P" + Conversions.ToString(num2) + ":P" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("P" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "F");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["P", Missing.Value], null, "ColumnWidth", new object[] { 3 }, null, null, false, true);
            tasWSheet.get_Range("Q" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Merge(Missing.Value);
            tasWSheet.get_Range("Q" + Conversions.ToString(num2), Missing.Value).set_Value(Missing.Value, "Tổng giờ công");
            NewLateBinding.LateSetComplex(tasWSheet.Columns["Q", Missing.Value], null, "ColumnWidth", new object[] { 7.5 }, null, null, false, true);
            NewLateBinding.LateSetComplex(tasWSheet.Rows["4:5", Missing.Value], null, "RowHeight", new object[] { 0x19 }, null, null, false, true);
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Bold = true;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Font.Size = 8;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).WrapText = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).Borders.LineStyle = 1;
            tasWSheet.get_Range("A" + Conversions.ToString(num2) + ":Q" + Conversions.ToString((int)(num2 + 1)), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignCenter;
            tasWSheet = null;
        }
        void InTongCong()
        {
             TasStartExcel("NVVESOM");
            int count = dt.Rows.Count;
            // doc do phan giai man hinh
            frmwaiting f = new frmwaiting();
            f.Show();
            f.lbgoi.Text = "Đang duyệt...";
            f.lbphantram.Visible = true;
            f.tocdo.Visible = true;
            f.tocdo.Maximum = count;
            f.Location = DoPhanGiaiManhinh();
            f.Activate();
            int i = 0;
            
            int chisodong =0;
            int phantram = 1;
            System.Windows.Forms.Application.DoEvents();
            if (dt.Rows.Count != 0)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet tasWSheet = TasWSheet;
                    TaoHeaderTongCong(1, dtpbd.Value.Date, dtpkt.Value.Date);
                    long songay = DateAndTime.DateDiff(DateInterval.Day, dtpbd.Value, dtpkt.Value, FirstDayOfWeek.System, FirstWeekOfYear.System) + 1;
                    long sodong = count / songay;
                    for (i = 0; i < sodong;i++ )
                    {
                        f.tocdo.Value = phantram;
                        f.lbphantram.Text = Conversions.ToString((double)(Math.Round((double)(((double)i) / ((double)sodong)), 2) * 100.0)) + "%";
                        f.Activate();
                        System.Windows.Forms.Application.DoEvents();
                        //lap de viet so dong 
                        DataRow row =dt.Rows[chisodong];
                        tasWSheet.get_Range("A" + Conversions.ToString(i+ 6), Missing.Value).set_Value(Missing.Value, (i + 1).ToString());
                        tasWSheet.get_Range("A" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + (i + 1).ToString());
                        tasWSheet.get_Range("B" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + row["TENPB"].ToString());
                        tasWSheet.get_Range("C" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + row["MANV"].ToString());
                        tasWSheet.get_Range("D" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + row["TENNV"].ToString());
                        tasWSheet.get_Range("E" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, "'" + row["TENCV"].ToString());
                        //tinh toan 
                        int solanlap = 0;
                        float tonggiocong = 0;
                        float tongngaycong = 0;
                        float tongtangcangaythuong = 0;
                        float tongtangcangaychunhat = 0;
                        float tongtangcangayle = 0;
                        float tongngaycongtac = 0;
                        float tongngaynghiphep = 0;
                        float tongcong = 0;
                        float tongtre = 0;
                        float tongsom = 0;
                        float tongcongtresom = 0;
                        while (solanlap < songay)
                        { 
                            DataRow dr =dt.Rows[chisodong];
                            //tinh tong tre
                            if(float.Parse(dr["TRE"].ToString())>0)
                            {
                                tongtre += float.Parse(dr["TRE"].ToString());
                            }
                            if (float.Parse(dr["SOM"].ToString()) > 0)
                            {
                                tongsom += float.Parse(dr["SOM"].ToString());
                            }
                            tonggiocong +=float.Parse( dr["GIOCONG"].ToString());
                            tongngaycong += float.Parse(dr["NGAYCONG"].ToString());
                            if (float.Parse(dr["TCNT"].ToString()) > 0)
                            {
                                tongtangcangaythuong += float.Parse(dr["TCNT"].ToString());
                            }
                            if (float.Parse(dr["TCCN"].ToString()) > 0)
                            {
                                tongtangcangaychunhat += float.Parse(dr["TCCN"].ToString()); 
                            }
                            if (float.Parse(dr["TCNL"].ToString()) > 0)
                            {
                                tongtangcangayle += float.Parse(dr["TCNL"].ToString());
                            }
                            if (dr["KYHIEU"].ToString() == "NP")
                            {
                                tongngaynghiphep++;
                            }
                            if (dr["KYHIEU"].ToString() == "CT")
                            {
                                tongngaycongtac++;
                            }
                            chisodong++;
                            solanlap++;
                        }
                        tongcongtresom = tongtre + tongsom;
                        tongcong = tonggiocong + tongtangcangayle + tongtangcangaythuong + tongtangcangaychunhat;
                        //thuc hien in binh thuong 
                        int dem = 0;
                        tasWSheet.get_Range("F" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tonggiocong.ToString());
                        tasWSheet.get_Range("G" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongngaycong.ToString());
                        tasWSheet.get_Range("H" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongtangcangaythuong.ToString());
                        tasWSheet.get_Range("I" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongtangcangaychunhat.ToString());
                        tasWSheet.get_Range("J" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongtangcangayle.ToString());
                        tasWSheet.get_Range("K" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongtre.ToString());
                        tasWSheet.get_Range("L" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongsom.ToString());
                        tasWSheet.get_Range("M" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongcongtresom.ToString());
                        tasWSheet.get_Range("N" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, dem.ToString());
                        tasWSheet.get_Range("O" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongngaycongtac.ToString());
                        tasWSheet.get_Range("P" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongngaynghiphep.ToString());
                        tasWSheet.get_Range("Q" + Conversions.ToString(i + 6), Missing.Value).set_Value(Missing.Value, tongcong.ToString());
                    }
                    TaoFooterTongCong(i+6);

                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Lỗi :" + ex.Message.ToString());
                    f.Close();
                }
                finally
                {
                    f.Close();
                }
            }
            else
            {
                frmHoidap hoi = new frmHoidap("Dữ liệu trống!", frmHoidap.CO.an, frmHoidap.KHONG.an, frmHoidap.THOAT.an, frmHoidap.HINH.baoloi);
                hoi.ShowDialog();
                f.Close();
            }
        }
        public void TaoFooterTongCong(int dong)
        {
            TasWSheet.get_Range("A" + Conversions.ToString(dong) + ":Q" + Conversions.ToString(dong), Missing.Value).Interior.Color = 0xc0c0c0;
            TasWSheet.get_Range("A6:Q" + Conversions.ToString(dong), Missing.Value).Borders.LineStyle = 1;
            if (dong > 7)
            {
                TasWSheet.get_Range("A6:Q" + Conversions.ToString((int)(dong - 1)), Missing.Value).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = 3;
            }
        }
        #endregion
        public delegate void _Close();
        public event _Close Close;
        private void simpleButton3_Click(object sender, EventArgs e)
        {
            if (this.Close != null)
                this.Close();
        }
    }
}
